package org.apache.ocean.database;

import java.io.IOException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.ocean.CLong;
import org.apache.ocean.ClusterConstants;
import org.apache.ocean.ClusterUtil;
import org.apache.ocean.NamedList;
import org.apache.ocean.util.PrintStringWriter;

/**
 * Web based access to the database 
 * 
 * 
 * @author jasonr
 */
// TODO: enable saving of sql statements 
public class SQLServlet extends HttpServlet {
	public static Logger log = Logger.getLogger(SQLServlet.class.getName());
	DatabaseRegistry databaseRegistry;

	public void init() throws ServletException {
		try {
			databaseRegistry = DatabaseRegistry.getDatabaseRegistry();
		} catch (Throwable throwable) {
			log.log(Level.SEVERE, "", throwable);
			throw new ServletException(throwable);
		}
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintStringWriter writer = new PrintStringWriter();
		try {
			response.setStatus(200);
			handleMain(request, response, writer);
		} catch (Throwable throwable) {
			log.log(Level.SEVERE, "", throwable);
			writer = new PrintStringWriter();
			response.setStatus(500);
			response.setContentType("text/plain");
			String stackTrace = ExceptionUtils.getFullStackTrace(throwable);
			writer.write(stackTrace);
		} finally {
			response.getWriter().write(writer.toString());
		}
	}

	private void handleMain(HttpServletRequest request, HttpServletResponse response, PrintStringWriter writer) throws Exception {
		response.setContentType(ClusterConstants.CONTENT_TYPE_HTML);
		writer.println("<form action='" + request.getRequestURI() + "' method=get>");
		// writer.println("<input type=hidden name=action value=displayMain>");
		writer.println("<select name='database'>");
		for (String databaseName : databaseRegistry.getNames()) {
			writer.println("<option value='" + databaseName + "'>" + databaseName + "</option>");
		}
		writer.println("</select>");
		String sql = request.getParameter("sql");
		CLong start = ClusterUtil.getLong("start", request);
		CLong rows = ClusterUtil.getLong("rows", request);
		writer.println("Start: <input type=text name=start value=" + ClusterUtil.toString(start) + ">");
		writer.println("Rows: <input type=text name=rows value=" + ClusterUtil.toString(rows) + ">");
		writer.println("<br>");
		writer.println("<textarea rows=10 cols=80 name=sql>" + ClusterUtil.toString(sql) + "</textarea>");
		writer.println("<input type=submit name=querySubmit value=Query>");
		writer.println("<input type=submit name=updateSubmit value=Update>");
		writer.println("</form><br>");
		String querySubmit = request.getParameter("querySubmit");
		String updateSubmit = request.getParameter("updateSubmit");
		String databaseName = request.getParameter("database");
		if (StringUtils.isNotBlank(databaseName)) {
			JDBCDatabase database = databaseRegistry.get(databaseName);
			if (StringUtils.isNotBlank(updateSubmit)) {
				handleUpdate(database, sql, writer);
			} else if (StringUtils.isNotBlank(querySubmit)) {
				if (StringUtils.isNotBlank(sql)) {
					printResults(database, sql, start, rows, writer);
				}
			}
		}
	}

	private void handleUpdate(JDBCDatabase database, String sql, PrintStringWriter writer) throws Exception {
		if (StringUtils.isNotBlank(sql)) {
			int rowsUpdated = database.executeUpdate(sql);
			writer.println("Rows updated: " + rowsUpdated + "<br>");
		}
	}

	private void printResults(JDBCDatabase database, String sql, CLong start, CLong rows, PrintStringWriter writer) throws Exception {
		SQLResults results = database.executeQuery(sql, (SQLParameters) null, null, start, rows);
		writer.println("<table border=0>");
		writer.println("<tr>");
		for (String columnName : results.getColumnNames()) {
			writer.println("<td>" + columnName.toLowerCase() + "</td>");
		}
		writer.println("</tr>");
		for (SQLRow row : results) {
			printRow(row, writer);
		}
		writer.println("</table>");
	}

	private void printRow(SQLRow row, PrintStringWriter writer) throws Exception {
		writer.println("<tr>");
		for (NamedList.Entry<String, Object> entry : row.getEntries()) {
			printCell(entry, writer);
		}
		writer.println("</tr>");
	}

	private void printCell(NamedList.Entry<String, Object> entry, PrintStringWriter writer) throws Exception {
		writer.print("<td>");
		if (entry.value == null) {
		} else if (entry.value instanceof String) {
			writer.print((String) entry.value);
		} else if (entry.value instanceof Date) {
			writer.print((Date) entry.value);
		} else if (entry.value instanceof CLong) {
			CLong l = (CLong) entry.value;
			writer.print(l.toString());
		} else {
			writer.print(entry.value.toString());
		}
		writer.println("</td>");
	}
}
